from app.utils.time import get_offset_date
from app.database.postgre import SessionDep, col, func, and_, or_, text, select, case
from app.database.models import App, Account, Device, Card, DeviceStatus
from app.models import responses
from app.models.common import DeviceIdJSON, AppIdParams, AppIdJSON
from app.models.devices import AppDeviceListParams, BatchDeleteAppDevicesJSON, DeviceCardsListParams


async def get_devices_list(account: Account, query_params: AppDeviceListParams, session: SessionDep):
    app_id = query_params.app_id
    page = query_params.page
    page_size = query_params.page_size
    used_card = query_params.used_card
    status = query_params.status
    device_code = query_params.device_code

    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    query = session.query(Device).filter(Device.app_id == app_id).order_by(col(Device.id).desc())

    if used_card:
        query = query.join(Device.card).filter(col(Card.card).like(f"%{used_card}%"))

    ping_duration = app.ping_duration * 60
    status_conditions = []
    if status == DeviceStatus.OFFLINE.value:
        status_conditions.append(or_(
            Device.token == '',
            col(Device.ping_time).is_(None),
            get_offset_date(-ping_duration) > Device.ping_time
        ))
    elif status == DeviceStatus.ONLINE.value:
        status_conditions.append(
            and_(
                Device.token != '',
                col(Device.ping_time).isnot(None),
                get_offset_date(-ping_duration) <= Device.ping_time
            )
        )

    if status_conditions:
        query = query.filter(or_(*status_conditions))
    if device_code:
        query = query.filter(col(Device.code).like(f"%{device_code}%"))

    sub_query = query.with_entities(func.count()).order_by(None)
    total_count = session.execute(sub_query).scalar_one()
    total_pages = (total_count + page_size - 1) // page_size

    offset = (page - 1) * page_size
    paginated_query = query.distinct(Device.id).offset(offset).limit(page_size)
    paginated_devices = paginated_query.all()

    results = []
    for device in paginated_devices:
        result = device.model_dump()
        result.update({'status': device.status, 'card': device.card.card})
        results.append(result)

    return responses.SuccessResponse(
        data={
            'total': total_count,
            'total_pages': total_pages,
            'page': page,
            'page_size': len(results),
            'list': results
        }
    )


async def get_device_used_cards(account: Account, query_params: DeviceCardsListParams, session: SessionDep):
    app_id = query_params.app_id
    device_code = query_params.device_code

    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    if app.force_disabled:
        return responses.Response304()

    devices = session.exec(
        select(Device)
        .where(
            Device.app_id == app_id,
            Device.code == device_code
        )
    ).all()

    if not devices:
        return responses.SuccessResponse(data={
            "used_cards": []
        })

    card_device_map = {d.card_id: d.id for d in devices if d.card_id is not None}
    card_ids = list(card_device_map.keys())

    cards = session.exec(
        select(Card)
        .where(
            col(Card.id).in_(card_ids),
            Card.app_id == app_id
        )
    ).all()

    used_cards = [{
        'card': card.card,
        'card_id': card.id,
        'device_id': card_device_map.get(card.id),
        'app_id': card.app_id
    } for card in cards]

    return responses.SuccessResponse(data={
        "used_cards": used_cards
    })


async def delete_device_by_id(account: Account, json_data: DeviceIdJSON, session: SessionDep):
    app_id = json_data.app_id
    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    if app.force_disabled:
        return responses.Response304()

    device = session.get(Device, json_data.device_id)
    if not device or device.app_id != app_id:
        return responses.Response603()

    session.delete(device)
    session.commit()

    return responses.SuccessResponse(msg='设备已删除', data=device.model_dump(include={'id'}))


async def delete_device_by_ids(account: Account, json_data: BatchDeleteAppDevicesJSON, session: SessionDep):
    """批量删除软件设备"""
    # 获取对应的应用
    app_id = json_data.app_id
    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    devices = session.query(Device).filter(
        Device.app_id == app_id, col(Device.id).in_(json_data.device_ids)
    ).all()
    if not devices:
        return responses.SuccessResponse(msg='没有找到指定的设备', data={'count': 0, 'ids': []})

    deleted_ids = [device.id for device in devices]

    session.query(Device).filter(
        Device.app_id == app_id, col(Device.id).in_(deleted_ids)
    ).delete(synchronize_session=False)

    session.commit()

    return responses.SuccessResponse(msg='选中的设备已删除', data={
        'count': len(deleted_ids),
        'ids': deleted_ids
    })


async def clear_all_app_devices(account: Account, json_data: AppIdJSON, session: SessionDep):
    """清空所有的设备"""
    # 获取对应的应用
    app_id = json_data.app_id
    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    # 获取该应用的所有设备
    devices = session.query(Device).filter(Device.app_id == app_id).all()
    if not devices:
        return responses.SuccessResponse(msg='该应用没有设备', data={'ids': []})

    deleted_ids = [device.id for device in devices]

    session.query(Device).filter(Device.app_id == app_id).delete(synchronize_session=False)
    session.commit()

    return responses.SuccessResponse(msg='设备已清空', data={
        'ids': deleted_ids
    })


async def logout_device(account: Account, json_data: DeviceIdJSON, session: SessionDep):
    app_id = json_data.app_id
    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    if app.force_disabled:
        return responses.Response304()

    device = session.get(Device, json_data.device_id)
    if not device or device.app_id != app_id:
        return responses.Response603()

    device.token = ''
    session.commit()
    session.refresh(device)
    return responses.SuccessResponse(data=device.model_dump(include={'id'}))


async def get_devices_online_count(account: Account, query_params: AppIdParams, session: SessionDep):
    app_id = query_params.app_id
    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    interval_seconds = app.ping_duration * 60
    interval_expr = text(
        "ping_time + (make_interval(secs => :secs))"
    ).bindparams(secs=interval_seconds)

    subquery = (
        select(
            col(Device.code).label("device_code"),
            func.max(Device.ping_time).label("latest_ping")
        )
        .where(Device.app_id == app_id)
        .group_by(Device.code)
        .subquery()
    )

    stmt = (
        select(
            func.sum(
                case(
                    (
                        (Device.token != '') &
                        (Device.ping_time == subquery.c.latest_ping) &
                        (func.now() <= interval_expr),
                        1
                    ),
                    else_=0
                )
            ).label("online_count"),
            func.sum(
                case(
                    (
                        (Device.token == '') |
                        ((Device.ping_time == subquery.c.latest_ping) &
                        (func.now() > interval_expr)),
                        1
                    ),
                    else_=0
                )
            ).label("offline_count")
        )
        .join(subquery, Device.code == subquery.c.device_code)
        .where(Device.app_id == app_id)
    )

    result = session.exec(stmt).one_or_none()
    online_count = result.online_count if result else 0
    offline_count = result.offline_count if result else 0

    return responses.SuccessResponse(data={
        "online_count": online_count,
        "offline_count": offline_count
    })
